﻿using Infrastructure.Dao;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WsdShop.IService;
using WsdShop.Model;
using WsdShop.Model.Enum;
using WsdShop.Model.SearchModels.Draw;
using WsdShop.Model.ViewModels.Draw;
using SyntacticSugar;
using Infrastructure;
using WsdShop.Model.ViewModels;

namespace WsdShop.Service
{
    public class DrawAdminService: IDrawAdminService
    {

        public List<DrawVM> GetDrawList(DrawSearchModel search,out int total)
        {
            using(var db = DbConfig.GetInstance())
            {
                db.CurrentFilterKey = "";
                string sql = $@"select t1.Id,t1.DrawName,AddTime,t1.StartTime,EndTime,Chances,ConsumePoints,GetPersonCount,IsDisplay,PrizeCount,Sort from Draw t1 
                                left join 
                                (select DrawId,COUNT(*) as GetPersonCount from DrawRecord where IsDeleted=0 and IsWinning=1 group by DrawId)
                                as t2 on t2.DrawId=t1.Id
                                left join(
                                select DrawId,COUNT(*) as PrizeCount from DrawReward where IsDeleted=0 group by DrawId
                                )as t3 on t3.DrawId=t1.Id";
                string where = " where t1.IsDeleted=0";
                if (search.IsActive==(int)ActiveType.有效)
                {
                    where += " and getdate()>t1.StartTime and getdate()<t1.EndTime and t1.IsDisplay=1";
                }
                else if(search.IsActive==(int)ActiveType.已过期)
                {
                    where += " and getdate()<t1.StartTime or getdate()>t1.EndTime or t1.IsDisplay=0";
                }

                if (search.StartTimeFirst != null)
                {
                    where += $@" and t1.StartTime>='{search.StartTimeFirst.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
                }

                if (search.StartTimeSec != null)
                {
                    where += $@" and t1.StartTime<='{search.StartTimeSec.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
                }

                if (!string.IsNullOrWhiteSpace(search.Keywords))
                {
                    where += $@" and t1.DrawName like '%{search.Keywords}%' or t1.Description like '%{search.Keywords}%'";
                }
                sql += where;

                //sql += " order by t1.Sort";

                //总行数
                //string sqlTotal = $"select count(*) from ({sql}) as tt";
                //total = Convert.ToInt32(db.GetDataTable(sqlTotal));
                total = db.GetDataTable(sql).TryToList<DrawVM>().Count();

                //生成行号
                var sqlRowNumber = "";
                if (!string.IsNullOrWhiteSpace(search.OrderByFields))
                {
                    sqlRowNumber = $"select row_number() over(order by {search.OrderByFields})rowNo,* from ({sql}) as tt";
                }
                else
                {
                    sqlRowNumber = $"select row_number() over(order by(select 0)) rowNo, *from ({sql})as tt";
                }
                //分页
                string sqlPage =
                    $"select top({search.Take})* from({sqlRowNumber}) as tt where rowNo>{search.Skip}";
                return db.GetDataTable(sqlPage).TryToList<DrawVM>();
            }
        }

        /// <summary>
        /// 获取所有可用的商品，用来供新建奖品时选择
        /// </summary>
        /// <returns></returns>
        public List<ProductVM> GetActProducts()
        {
            using(var db = DbConfig.GetInstance())
            {
                var model = db.Queryable<Product>().Where(p => p.IsDeleted == false && p.IsDisplay == true).Select<Product,ProductVM>(p=>new ProductVM { Id=p.Id,Name=p.Name}).ToList();
                return model;
            }
        }

        public List<ProductVM> GetActCoupons()
        {
            using(var db = DbConfig.GetInstance())
            {
                var model = db.Queryable<Coupon>().Where(c => c.IsDeleted == false).Select<Coupon,ProductVM>(c=>new ProductVM{Id= c.Id,Name=c.CouponName}).ToList();
                return model;
            }
        }

        public List<DrawRewardVM> GetRewardList(DrawRewardSearchModel search,out int total)
        {
            using(var db = DbConfig.GetInstance())
            {
                string sql = $@"select * from DrawReward where DrawId={search.DrawId} and IsDeleted=0 ";
                
                total = db.GetDataTable(sql).TryToList<DrawRewardVM>().Count();

                //生成行号
                var sqlRowNumber = "";
                if (!string.IsNullOrWhiteSpace(search.OrderByFields))
                {
                    sqlRowNumber = $"select row_number() over(order by {search.OrderByFields})rowNo,* from ({sql}) as tt";
                }
                else
                {
                    sqlRowNumber = $"select row_number() over(order by(select 0)) rowNo, *from ({sql})as tt";
                }
                //分页
                string sqlPage =
                    $"select top({search.Take})* from({sqlRowNumber}) as tt where rowNo>{search.Skip}";
                return db.GetDataTable(sqlPage).TryToList<DrawRewardVM>();
            }
        }

        /// <summary>
        /// 保存奖项，将更新所有奖项的StartValue和EndValue        
        /// <summary>
        /// <param name="reward"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public Result SaveReward(DrawReward reward, Result result)
        {
            using(var db = DbConfig.GetInstance())
            {
                var list = db.Queryable<DrawReward>().Where(d => d.DrawId == reward.DrawId && d.IsDeleted == false).ToList();
                #region 新增
                if (reward.Id == 0)
                {
                    if (list.Count >= 11)
                    {
                        result.isSuccess = false;
                        result.msg = "奖项数量不能超过11个";
                        return result;
                    }
                    int totalRate = list.Sum(d=>d.WinRate)+reward.WinRate;
                    if (totalRate > 100)
                    {
                        result.isSuccess = false;
                        result.msg = "中奖率总和不得超过100";
                        return result;
                    }
                    int total = 1;
                    //更新原有的
                    foreach(var item in list)
                    {
                        if (item.WinRate <= 0)
                        {
                            item.StartValue = 0;
                            item.EndValue = 0;
                        }
                        else
                        {
                            item.StartValue = total == 1 ? 1 : total;
                            total = total + item.WinRate;
                            item.EndValue = total;
                        }
                    }
                    db.UpdateRange(list);
                    //插入新增的
                    if (reward.WinRate <= 0)
                    {
                        reward.StartValue = 0;
                        reward.EndValue = 0;
                    }
                    else
                    {
                        reward.StartValue = total == 1 ? 1 : total;
                        total = total + reward.WinRate;
                        reward.EndValue = total;
                    }
                    db.Insert(reward);
                    result.isSuccess = true;
                    result.msg = "保存成功";
                    return result;
                }
                #endregion
                #region 修改
                else
                {
                    list = list.Where(d => d.Id != reward.Id).ToList();
                    int totalRate = list.Sum(d => d.WinRate) + reward.WinRate;
                    if (totalRate > 100)
                    {
                        result.isSuccess = false;
                        result.msg = "中奖率总和不得超过100";
                        return result;
                    }

                    int total = 1;
                    //更新原有的
                    foreach (var item in list)
                    {
                        if (item.WinRate <= 0)
                        {
                            item.StartValue = 0;
                            item.EndValue = 0;
                        }
                        else
                        {
                            item.StartValue = total == 1 ? 1 : total;
                            total = total + item.WinRate;
                            item.EndValue = total;
                        }
                    }
                    db.UpdateRange(list);
                    //插入新增的
                    if (reward.WinRate <= 0)
                    {
                        reward.StartValue = 0;
                        reward.EndValue = 0;
                    }
                    else
                    {
                        reward.StartValue = total == 1 ? 1 : total;
                        total = total + reward.WinRate;
                        reward.EndValue = total;
                    }
                    db.Update(reward);
                    result.isSuccess = true;
                    result.msg = "编辑成功";
                    return result;
                }
                #endregion
            }
        }

        public List<DrawRecordVM> GetRecordList(DrawRecordSearchModel search, out int total)
        {
            using(var db = DbConfig.GetInstance())
            {
                db.CurrentFilterKey = "";
                string sql = $@"select * from DrawRecord ";
                string where = " where IsDeleted=0 ";
                if (search.DrawId > 0)
                {
                    where += $@"and DrawId={search.DrawId} ";
                }
                if (search.AddTimeFirst != null)
                {
                    where += $@"and AddTime>='{search.AddTimeFirst.Value.ToString("yyyy - MM - dd HH: mm: ss")}' ";
                }
                if (search.AddTimeSec != null)
                {
                    where += $@"and AddTime<='{search.AddTimeSec.Value.ToString("yyyy - MM - dd HH: mm: ss")}' ";
                }
                if (!string.IsNullOrWhiteSpace(search.Keywords))
                {
                    where += $@"and MemberName like '%{search.Keywords}%' or DrawRewardName like '%{search.Keywords}%' or ProductName like '%{search.Keywords}%' ";
                }
                if (search.IsWin == 0)
                {
                    where += "and IsWinning=1 ";
                }
                else if(search.IsWin == 1)
                {
                    where += "and IsWinning=0 ";
                }
                sql += where;

                total = db.GetDataTable(sql).TryToList<DrawRecordVM>().Count();

                //生成行号
                var sqlRowNumber = "";
                if (!string.IsNullOrWhiteSpace(search.OrderByFields))
                {
                    sqlRowNumber = $"select row_number() over(order by {search.OrderByFields})rowNo,* from ({sql}) as tt";
                }
                else
                {
                    sqlRowNumber = $"select row_number() over(order by(select 0)) rowNo, *from ({sql})as tt";
                }
                //分页
                string sqlPage =
                    $"select top({search.Take})* from({sqlRowNumber}) as tt where rowNo>{search.Skip}";
                return db.GetDataTable(sqlPage).TryToList<DrawRecordVM>();

            }
        }

        public SpecResponse GetSpecById(int id)
        {
            using(var db = DbConfig.GetInstance())
            {
                string sql = $@"select t1.Id,t1.Price,t1.Stock,t1.SpecNames,t2.ImgUrl,t2.Name,t1.WaitSendStock from Product_Spec t1
                                left join(
                                select * from Product 
                                )as t2 on t1.ProductSku=t2.Sku where t1.Id={id}";
                var model = db.GetDataTable(sql).TryToList<SpecResponse>().FirstOrDefault();
                return model;
            }
        }
    }
}
